﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using RoadTeam.Model;
using RoadTeam.DAL;
using RoadTeam.Enums;
using RoadTeam.Pub;

public partial class 分页_StrPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Label4.Text = @"1"; BindGvList(0); } }
    protected void Button2_Click(object sender, EventArgs e) { BindGvList(1); }
    protected void Button1_Click(object sender, EventArgs e) { BindGvList(-1); }


    public void BindGvList(int i)
    {
        QueryPage page = new QueryPage();
        int CurrentPage = String.IsNullOrEmpty(Label4.Text) ? 0 : Convert.ToInt32(Label4.Text);
        CurrentPage = CurrentPage + i;
        page.CurrentPage = CurrentPage;
        page.PageSize = 10;
        page.OrderByColumn = @"UnitCode";
        String PageInfo = GetSelectUnitStr();
        Response.Write(GetPageSqlStr(page, PageInfo));
        String StrSql = GetPageSqlStr(page, PageInfo);
        DataTable dt = DbFactory.CreateInstance().CreateDb().
            ExecuteStrQuery(StrSql, CommandType.Text, GlobalConfig.RtConnectionString);
        DataTable Unit_dt = null;
        if (dt.Rows.Count != 0)
        {
            Unit_dt = SyncUnitData(dt);
            GridView3.DataSource = Unit_dt;
            GridView3.DataBind();
        }
        //同步后获取客户信息
        //GridView2.DataSource = SyncCustData(Unit_dt);
        //GridView2.DataBind();
        SyncCustData(Unit_dt);
        Label4.Text = page.CurrentPage.ToString();
    }

    private DataTable SyncCustData(DataTable Unit_dt)
    {
        if (Unit_dt == null || Unit_dt.Rows.Count == 0) return new DataTable();
        List<String> LiUnitIdentifier = new List<string>();
        String StrUnitIdentifier = String.Empty;
        foreach (DataRow dr in Unit_dt.Rows)
        {
            if (dr["UnitIdentifier"] == null || LiUnitIdentifier.Contains(dr["UnitIdentifier"].ToString())) continue;
            LiUnitIdentifier.Add(dr["UnitIdentifier"].ToString());
            StrUnitIdentifier += "'" + dr["UnitIdentifier"].ToString() + "',";
        }

        if (String.IsNullOrEmpty(StrUnitIdentifier)) return new DataTable();

        StrUnitIdentifier = StrUnitIdentifier.TrimEnd(',');

        DataTable dt_Cust = GetCustData(StrUnitIdentifier);
        DataTable dt_Cust_Sys = GetCustDataSys(StrUnitIdentifier);

        //同步后获取客户信息
        GridView2.DataSource = dt_Cust_Sys;
        GridView2.DataBind();



        #region 同步表中已经存在的客户信息
        String Rtidentity = @"CustIdentifier";
        String idtntity = @"CustIdentifier";
        String EnmunType = @"CustColumnDictionary";
        RoadTeam.Pub.SyncTable.SyncDataTable(dt_Cust, dt_Cust_Sys, Rtidentity, idtntity, EnmunType);
        RmCust.Update(dt_Cust_Sys, GlobalConfig.ConnectionString);
        #endregion

        //如果不存在,添加新列并且插入表
        DataRow dr_Sys_Cust;
        String CustIdentifier = "CustIdentifier";
        foreach (DataRow dr_Cust in dt_Cust.Rows)
        {
            if (dr_Cust["CustIdentifier"] != null && !String.IsNullOrEmpty(dr_Cust["CustIdentifier"].ToString())) continue;

            //添加判断,如果同种类型的房间已经存在,则跳过此处赋值

            DataRow[] drs_Sys = dt_Cust_Sys.Select(" CustIdentifier IS NOT NULL AND RelaType='" + dr_Cust["RelaType"].ToString() + "' AND  UnitIdentifier='" + dr_Cust["UnitIdentifier"].ToString() + "'");
            if (drs_Sys.Count() != 0) continue;

            dr_Sys_Cust = dt_Cust_Sys.NewRow();
            foreach (DataColumn Rt_Cust_Dc in dt_Cust.Columns)
            {

                foreach (DataColumn Dc in dt_Cust_Sys.Columns)
                {
                    if (Rt_Cust_Dc.ColumnName.ToLower() == Dc.ColumnName.ToLower())
                    {
                        if (Rt_Cust_Dc.ColumnName.ToLower() != CustIdentifier.ToLower() && Dc.ColumnName.ToLower() != CustIdentifier.ToLower())
                        {
                            dr_Sys_Cust[Dc.ColumnName.ToLower()] = dr_Cust[Rt_Cust_Dc.ColumnName.ToLower()];
                        }
                        else
                        {
                            String Identifier = CreateIdentifier.GetIdentifier();
                            dr_Sys_Cust[Dc.ColumnName.ToLower()] = Identifier;
                            dr_Cust[Rt_Cust_Dc.ColumnName.ToLower()] = Identifier;
                        }
                    }
                }
            }
            dt_Cust_Sys.Rows.Add(dr_Sys_Cust);
        }

        RmCust.InsertDatatable(dt_Cust_Sys, GlobalConfig.ConnectionString);

        GridView4.DataSource = dt_Cust_Sys;
        GridView4.DataBind();

        GridView5.DataSource = dt_Cust;
        GridView5.DataBind();

        foreach (DataRow dr in dt_Cust.Rows)
        {
            Response.Write(dr.RowState.ToString());
        }




        UpdateCustData(dt_Cust);

        return dt_Cust_Sys;
    }

    private DataTable GetCustDataSys(string StrUnitIdentifier)
    {
        SqlWhere where = new SqlWhere();
        where.StrSqlWhere = @"  UnitIdentifier IN (" + StrUnitIdentifier + ") ";
        return RmCust.Select(where, GlobalConfig.ConnectionString);
    }

    public DataTable SyncUnitData(DataTable dt)
    {
        SyncBuildingPavilionData(dt);
        List<String> liUnitIdentifier = new List<string>();
        String StrUnitIdentifiers = @"'-1'";
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["UnitIdentifier"] != null && !String.IsNullOrEmpty(dr["UnitIdentifier"].ToString()))
            {
                StrUnitIdentifiers += ",'" + dr["UnitIdentifier"].ToString() + "'";
                liUnitIdentifier.Add(dr["UnitIdentifier"].ToString());
            }
        }
        DataTable dt_Unit = GetUnitTable(StrUnitIdentifiers);
        DataRow[] drs;
        drs = dt.Select(@" UnitIdentifier IS NULL ");
        if (drs.Count() != 0)
        {
            //InsertUnitInfoSync(ToDataTable(drs), dt_Unit);
            InsertUnitInfoSync(dt, dt_Unit);
        }
        //根据Bs系统中的分页数据查找Server数据库中[RmUnit2]中的数据
        //更新Server数据库[RmUnit2]中的已经存在的数据
        //在Server数据库中[RmUnit2]表中没有查找到的,新建列插入Server数据库中



        if (liUnitIdentifier.Count > 0)//如果已经存在在Server数据库,查找出来并且更新
        {
            drs = dt.Select(@" UnitIdentifier IN (" + StrUnitIdentifiers + ")");
            UpdateUnitInfoSync(ToDataTable(drs), dt_Unit);
        }

        TbRmUnit.Update(dt, GlobalConfig.RtConnectionString);

        RmUnit2.InsertDatatable(dt_Unit, GlobalConfig.ConnectionString);
        RmUnit2.Update(dt_Unit, GlobalConfig.ConnectionString);

        GridView1.DataSource = dt;
        GridView1.DataBind();

        return dt_Unit;
    }
    private void InsertUnitInfoSync(DataTable Rt_Unit, DataTable dt_Unit)
    {
        DataRow dr_Unit;
        String UnitIdentifier = @"UnitIdentifier";
        foreach (DataRow Rt_Dr in Rt_Unit.Rows)
        {
            if (Rt_Dr["UnitIdentifier"] != null && !String.IsNullOrEmpty(Rt_Dr["UnitIdentifier"].ToString())) continue;

            dr_Unit = dt_Unit.NewRow();
            foreach (DataColumn Rt_Dc in Rt_Unit.Columns)
            {
                foreach (DataColumn Dc in dt_Unit.Columns)
                {
                    if (Rt_Dc.ColumnName.ToLower() == Dc.ColumnName.ToLower())
                    {
                        if (Rt_Dc.ColumnName.ToLower() != UnitIdentifier.ToLower() && Dc.ColumnName.ToLower() != UnitIdentifier.ToLower())
                        {
                            dr_Unit[Dc.ColumnName.ToLower()] = Rt_Dr[Rt_Dc.ColumnName.ToLower()];
                        }
                        else
                        {
                            String Identifier = CreateIdentifier.GetIdentifier();
                            dr_Unit[Dc.ColumnName.ToLower()] = Identifier;
                            Rt_Dr[Rt_Dc.ColumnName.ToLower()] = Identifier;
                        }
                    }
                }
            }
            dt_Unit.Rows.Add(dr_Unit);
        }
    }
    public void SyncBuildingPavilionData(DataTable Rt_Unit)
    {
        #region 同步表中楼阁,楼盘信息
        #region 更新楼阁标识
        List<string> li = new List<string>();
        String StrIn = String.Empty;
        foreach (DataRow dr in Rt_Unit.Rows)
        {
            if (dr["PavilionID"] != null && !li.Contains(dr["PavilionID"].ToString()))
            {
                li.Add(dr["PavilionID"].ToString());
                StrIn += "'" + dr["PavilionID"].ToString() + "',";
            }
        }

        DataTable dt_Pavilion = GetPavilionData(StrIn.TrimEnd(','));
        DataRow dr_Pavilion;
        foreach (DataRow dr in Rt_Unit.Rows)
        {
            dr_Pavilion = dt_Pavilion.Select(@"PavilionID=" + dr["PavilionID"].ToString())[0];
            if (dr_Pavilion["PavilionIdentifier"] == null || String.IsNullOrEmpty(dr_Pavilion["PavilionIdentifier"].ToString()))
            {
                dr_Pavilion["PavilionIdentifier"] = CreateIdentifier.GetIdentifier();
                dr["PavilionIdentifier"] = dr_Pavilion["PavilionIdentifier"];
            }
            else
            {
                dr["PavilionIdentifier"] = dr_Pavilion["PavilionIdentifier"];
            }
        }
        UpdatePavilionData(dt_Pavilion);
        #endregion
        #region 更新楼盘标识
        li = new List<string>();
        StrIn = String.Empty;
        foreach (DataRow dr in Rt_Unit.Rows)
        {
            if (dr["BuildingID"] != null && !li.Contains(dr["BuildingID"].ToString()))
            {
                li.Add(dr["BuildingID"].ToString());
                StrIn += "'" + dr["BuildingID"].ToString() + "',";
            }
        }

        DataTable dt_Building = GetBuildingData(StrIn.TrimEnd(','));
        DataRow dr_Building;
        foreach (DataRow dr in Rt_Unit.Rows)
        {
            dr_Building = dt_Building.Select(@"BuildingID=" + dr["BuildingID"].ToString())[0];
            if (dr_Building["BuildingIdentifier"] == null || String.IsNullOrEmpty(dr_Building["BuildingIdentifier"].ToString()))
            {
                dr_Building["BuildingIdentifier"] = CreateIdentifier.GetIdentifier();
                dr["BuildingIdentifier"] = dr_Building["BuildingIdentifier"];
            }
            else
            {
                dr["BuildingIdentifier"] = dr_Building["BuildingIdentifier"];
            }
        }
        UpdateBuildingData(dt_Building);
        #endregion

        #region 更新单元标识
        // UnitID,UnitIdentifier 
        //li = new List<string>();
        //StrIn = String.Empty;
        //foreach (DataRow dr in Rt_Unit.Rows)
        //{
        //    if (dr["UnitID"] != null && !li.Contains(dr["UnitID"].ToString()))
        //    {
        //        li.Add(dr["UnitID"].ToString());
        //        StrIn += "'" + dr["UnitID"].ToString() + "',";
        //    }
        //}

        //DataTable dt_Unit = GetUnitData(StrIn.TrimEnd(','));
        //DataRow dr_Unit;
        //foreach (DataRow dr in Rt_Unit.Rows)
        //{
        //    dr_Unit = dt_Unit.Select(@"UnitID=" + dr["UnitID"].ToString())[0];
        //    if (dr_Unit["UnitIdentifier"] == null || String.IsNullOrEmpty(dr_Unit["UnitIdentifier"].ToString()))
        //    {
        //        dr_Unit["UnitIdentifier"] = CreateIdentifier.GetIdentifier();
        //        dr["UnitIdentifier"] = dr_Unit["UnitIdentifier"];
        //    }
        //    else
        //    {
        //        dr["UnitIdentifier"] = dr_Unit["UnitIdentifier"];
        //    }
        //}
        //UpdateUnitData(dt_Unit);
        #endregion

        #endregion
    }
    private void UpdateUnitInfoSync(DataTable Rt_Unit, DataTable dt_Unit)
    {
        #region 同步表中单元信息
        String Rtidentity = @"UnitIdentifier";
        String idtntity = @"UnitIdentifier";
        String EnmunType = @"UnitColumnDictionary2";
        RoadTeam.Pub.SyncTable.SyncDataTable(Rt_Unit, dt_Unit, Rtidentity, idtntity, EnmunType);
        RmUnit2.Update(dt_Unit, GlobalConfig.ConnectionString);
        #endregion
    }
    public DataTable ToDataTable(DataRow[] rows)
    {
        if (rows == null || rows.Length == 0) return null;
        DataTable tmp = rows[0].Table.Clone();  // 复制DataRow的表结构
        foreach (DataRow row in rows)
            //    tmp.Rows.Add(row);  // 将DataRow添加到DataTable中
            tmp.ImportRow(row);
        return tmp;
    }
    #region 更新楼盘楼阁方法
    public DataTable GetPavilionData(String Where)
    {
        SqlWhere where = new SqlWhere();
        where.StrSqlWhere = @"PavilionID in (" + Where + ")";
        where.SelectColumn = @" PavilionID,PavilionIdentifier ";
        return TbRmPavilion.Select(where, GlobalConfig.RtConnectionString);
    }
    public bool UpdatePavilionData(DataTable dt)
    {
        return TbRmPavilion.Update(dt, GlobalConfig.RtConnectionString);
    }
    public DataTable GetBuildingData(String Where)
    {
        SqlWhere where = new SqlWhere();
        where.StrSqlWhere = @"BuildingID in (" + Where + ")";
        where.SelectColumn = @" BuildingID,BuildingIdentifier ";
        return TbRmBuilding.Select(where, GlobalConfig.RtConnectionString);
    }
    public bool UpdateBuildingData(DataTable dt)
    {
        return TbRmBuilding.Update(dt, GlobalConfig.RtConnectionString);
    }

    public DataTable GetCustData(String Where)
    {
        String StrSql = @"SELECT TbRmCust.CustID,CustName,CustCode,BirthDate,Email,
Tel,RelaType,TbRmUnit.UnitIdentifier,TbRmCust.CustIdentifier FROM TbRmCust 
Inner JOIN TbRmCustUnitRela
on TbRmCust.CustID =TbRmCustUnitRela.CustID
INNER join TbRmUnit on TbRmUnit.UnitID=TbRmCustUnitRela.UnitID
WHERE TbRmUnit.UnitIdentifier  IN (" + Where + ")";
        DataTable dt = DbFactory.CreateInstance().CreateDb().ExecuteStrQuery(StrSql, CommandType.Text, GlobalConfig.RtConnectionString);
        return dt;
    }

    /// <summary>
    /// 更新系统中的客户表
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public bool UpdateCustData(DataTable dt)
    {
        return TbRmCust.Update(dt, GlobalConfig.RtConnectionString);
    }

    #endregion
    private DataTable GetUnitTable(string StrUnitIdentifiers)
    {
        SqlWhere where = new SqlWhere();
        where.StrSqlWhere = @"  UnitIdentifier IN (" + StrUnitIdentifiers + ") ";
        return RmUnit2.Select(where, GlobalConfig.ConnectionString);
    }
    public String GetSelectUnitStr()
    {
        String Str = @"SELECT  TbRmUnit.UnitID, [Floor],FloorCode,FloorName,
UnitCode,UnitName,UnitFullName,TbCsDoorType.DoorTypeID,
TbCsDoorType.App_Identifier AS DoorTypeIdentifier,
TbRmUnit.UnitIdentifier as [UnitIdentifier],
TbRmPavilion.PavilionID,PavilionIdentifier,
TbRmPavilion.PavilionCode,TbRmPavilion.PavilionName,
TbRmBuilding.BuildingID,BuildingIdentifier,
TbRmBuilding.BuildingCode,TbRmBuilding.BuildingName,
TbRmBuilding.OrganID,TbSmOrgan.OrganIdentifier
FROM TbRmUnit  
INNER JOIN  TbRmPavilion
on TbRmUnit.PavilionID=TbRmPavilion.PavilionID
INNER JOIN TbRmBuilding 
on TbRmBuilding.BuildingID=TbRmPavilion.BuildingID
INNER join TbSmOrgan on TbRmBuilding.OrganID =TbSmOrgan.OrganID
INNER JOIN TbCsDoorType on TbCsDoorType.DoorTypeID=TbRmUnit.DoorTypeID
WHERE TbRmBuilding.OrganID=218
ORDER BY TbRmUnit.UnitID";
        return Str;
    }

    public String GetSelectcustStr(String Where)
    {
        String Str = @"SELECT TbRmCust.CustID,CustName,CustCode,BirthDate,Email,
Tel,RelaType,TbRmUnit.UnitIdentifier FROM TbRmCust 
Inner JOIN TbRmCustUnitRela
on TbRmCust.CustID =TbRmCustUnitRela.CustID
INNER join TbRmUnit on TbRmUnit.UnitID=TbRmCustUnitRela.UnitID
WHERE TbRmUnit.UnitIdentifier  IN (" + Where + ")";
        return Str;
    }


    public String GetPageSqlStr(QueryPage Page, String StrSqlInfo)
    {
        String Str = MakePageSQLStringByMSSQL(StrSqlInfo, String.Empty, Page.PageSize, Page.CurrentPage, 1000);
        return Str;
    }
    private string MakePageSQLStringByMSSQL(string strSQLInfo, string strWhere, int PageSize, int PageNumber, int AllCount)
    {
        #region 分页位置分析
        string strSQLType = string.Empty;
        if (AllCount > 0)
        {
            if (PageNumber == 1) //首页
            {
                strSQLType = "First";
            }
            else if (PageSize * PageNumber > AllCount) //最后的页 @@LeftSize
            {
                PageSize = AllCount - PageSize * (PageNumber - 1);
                strSQLType = "Last";
            }
            else //中间页
            {
                strSQLType = "Mid";
            }
        }
        else if (AllCount < -1) //特殊处理 
        {
            strSQLType = "First";
        }
        else
        {
            strSQLType = "Count";//0 or -1
        }

        #endregion

        #region SQL 复杂度分析
        //SQL 复杂度分析 开始
        bool SqlFlag = true;//简单SQL标记
        string TestSQL = strSQLInfo.ToUpper();
        int n = TestSQL.IndexOf("SELECT ", 0);
        n = TestSQL.IndexOf("SELECT ", n + 7);
        if (n == -1)
        {
            //可能是简单的查询，再次处理
            n = TestSQL.IndexOf(" JOIN ", n + 7);
            if (n != -1) SqlFlag = false;
            else
            {
                //判断From 谓词情况
                n = TestSQL.IndexOf("FROM ", 9);
                if (n == -1) return "";
                //计算 WHERE 谓词的位置
                int m = TestSQL.IndexOf("WHERE ", n + 5);
                // 如果没有WHERE 谓词
                if (m == -1) m = TestSQL.IndexOf("ORDER BY ", n + 5);
                //如果没有ORDER BY 谓词，那么无法排序，退出；
                if (m == -1)
                    throw new Exception("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n" + strSQLInfo);
                string strTableName = TestSQL.Substring(n, m - n);
                //表名中有 , 号表示是多表查询
                if (strTableName.IndexOf(",") != -1)
                    SqlFlag = false;
            }
        }
        else
        {
            //有子查询；
            SqlFlag = false;
        }
        //SQL 复杂度分析 结束
        #endregion

        #region 排序语法分析
        //排序语法分析 开始
        int iOrderAt = strSQLInfo.ToLower().LastIndexOf("order by ");
        //如果没有ORDER BY 谓词，那么无法排序分页，退出；
        if (iOrderAt == -1)
        {
            if (PageNumber == 1)
            {
                string sqlTemp = "Select Top @@PageSize * FROM ( " + strSQLInfo +
                       " ) P_T0 @@Where ";
                return sqlTemp.Replace("@@PageSize", PageSize.ToString()).Replace("@@Where", strWhere);
            }
            else
            {
                throw new Exception("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n" + strSQLInfo);
            }
        }

        string strOrder = strSQLInfo.Substring(iOrderAt + 9);
        strSQLInfo = strSQLInfo.Substring(0, iOrderAt);
        string[] strArrOrder = strOrder.Split(new char[] { ',' });
        for (int i = 0; i < strArrOrder.Length; i++)
        {
            string[] strArrTemp = (strArrOrder[i].Trim() + " ").Split(new char[] { ' ' });
            //压缩多余空格
            for (int j = 1; j < strArrTemp.Length; j++)
            {
                if (strArrTemp[j].Trim() == "")
                {
                    continue;
                }
                else
                {
                    strArrTemp[1] = strArrTemp[j];
                    if (j > 1) strArrTemp[j] = "";
                    break;
                }
            }
            //判断字段的排序类型
            switch (strArrTemp[1].Trim().ToUpper())
            {
                case "DESC":
                    strArrTemp[1] = "ASC";
                    break;
                case "ASC":
                    strArrTemp[1] = "DESC";
                    break;
                default:
                    //未指定排序类型，默认为降序
                    strArrTemp[1] = "DESC";
                    break;
            }
            //消除排序字段对象限定符
            if (strArrTemp[0].IndexOf(".") != -1)
                strArrTemp[0] = strArrTemp[0].Substring(strArrTemp[0].IndexOf(".") + 1);
            strArrOrder[i] = string.Join(" ", strArrTemp);

        }
        //生成反向排序语句
        string strNewOrder = string.Join(",", strArrOrder).Trim();
        strOrder = strNewOrder.Replace("ASC", "ASC0").Replace("DESC", "ASC").Replace("ASC0", "DESC");
        //排序语法分析结束
        #endregion

        #region 构造分页查询
        string SQL = string.Empty;
        if (!SqlFlag)
        {
            //复杂查询处理
            switch (strSQLType.ToUpper())
            {
                case "FIRST":
                    SQL = "Select Top @@PageSize * FROM ( " + strSQLInfo +
                        " ) P_T0 @@Where ORDER BY " + strOrder;
                    break;
                case "LAST":
                case "MID":
                    string strRep = "SELECT SELECT# FROM (" + strSQLInfo + ") AS P_T0 @@Where"; //重要，必须放置在此。
                    strRep = strRep.Replace("SELECT#", "ROW_NUMBER() OVER (ORDER BY " + strOrder + ") AS SYS_PG_0000,*");
                    SQL = "SELECT * FROM (" + strRep + ") AS P_T1 WHERE SYS_PG_0000 > @@Page_Size_Number_Start AND SYS_PG_0000 <= @@Page_Size_Number_End";
                    SQL = SQL.Replace("@@Page_Size_Number_Start", Convert.ToString((PageNumber - 1) * PageSize));
                    SQL = SQL.Replace("@@Page_Size_Number_End", Convert.ToString((PageNumber) * PageSize));
                    //                        SQL = @"SELECT Top @@PageSize * FROM
                    //                         (SELECT Top @@PageSize * FROM
                    //                           (
                    //                             SELECT Top @@Page_Size_Number * FROM (";
                    //                        SQL += " " + strSQLInfo + " ) P_T0 @@Where ORDER BY " + strOrder + " ";
                    //                        SQL += @") P_T1
                    //            ORDER BY " + strNewOrder + ") P_T2  " +
                    //                            "ORDER BY " + strOrder;
                    break;
                //                    case "LAST":
                //                        SQL = @"SELECT * FROM (     
                //                          Select Top @@LeftSize * FROM (" + " " + strSQLInfo + " ";
                //                        SQL += " ) P_T0 @@Where ORDER BY " + strNewOrder + " " +
                //                            " ) P_T1 ORDER BY " + strOrder;
                //                        break;
                case "COUNT":
                    SQL = "SELECT COUNT(1) FROM ( " + strSQLInfo + " ) P_Count @@Where";
                    break;
                default:
                    SQL = strSQLInfo + strOrder;//还原
                    break;
            }

        }
        else
        {
            //简单查询处理
            string strUpperSQLInfo = strSQLInfo.ToUpper();
            bool isDistinct = strUpperSQLInfo.IndexOf("DISTINCT") >= 7;
            if (isDistinct)
                strUpperSQLInfo = strUpperSQLInfo.Replace("DISTINCT", "");
            switch (strSQLType.ToUpper())
            {
                case "FIRST":
                    if (isDistinct)
                        SQL = strUpperSQLInfo.Replace("DISTINCT", "DISTINCT TOP @@PageSize");
                    else
                        SQL = strUpperSQLInfo.Replace("SELECT ", "SELECT TOP @@PageSize ");
                    SQL += "  @@Where ORDER BY " + strOrder;
                    break;
                case "LAST":
                case "MID":
                    string strRep = "SELECT SELECT# FROM (" + strSQLInfo + "@@Where" + ") AS A WHERE 1=1"; //重要，必须放置在此。
                    if (isDistinct)
                        strRep = strRep.Replace("SELECT#", "DISTINCT SELECT#");
                    strRep = strRep.Replace("SELECT#", "ROW_NUMBER() OVER (ORDER BY " + strOrder + ") AS SYS_PG_0000,*");
                    SQL = "SELECT * FROM (" + strRep + ") AS P_T0 WHERE SYS_PG_0000 > @@Page_Size_Number_Start AND SYS_PG_0000 <= @@Page_Size_Number_End";
                    SQL = SQL.Replace("@@Page_Size_Number_Start", Convert.ToString((PageNumber - 1) * PageSize));
                    SQL = SQL.Replace("@@Page_Size_Number_End", Convert.ToString((PageNumber) * PageSize));
                    break;
                case "COUNT":
                    SQL = "SELECT COUNT(*) FROM ( " + strSQLInfo + " @@Where) P_Count ";
                    break;
                default:
                    SQL = strSQLInfo + strOrder;//还原
                    break;
            }
        }

        //执行分页参数替换
        if (PageSize < 0) PageSize = 0;
        SQL = SQL.Replace("@@PageSize", PageSize.ToString())
            .Replace("@@Page_Size_Number", Convert.ToString(PageSize * PageNumber))
            .Replace("@@LeftSize", PageSize.ToString());//
        //.Replace ("@@Where",strWhere);
        //针对用户的额外条件处理：
        if (strWhere == null) strWhere = "";
        if (strWhere != "" && strWhere.ToUpper().Trim().StartsWith("WHERE "))
        {
            throw new Exception("分页额外查询条件不能带Where谓词！");
        }
        if (!SqlFlag)
        {
            if (strWhere != "") strWhere = " Where " + strWhere;
            SQL = SQL.Replace("@@Where", strWhere);
        }
        else
        {
            if (strWhere != "") strWhere = " And (" + strWhere + ")";
            SQL = SQL.Replace("@@Where", strWhere);
        }
        return SQL;
        #endregion

    }
}


